﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyQuanCafe.DataAccess;
using QuanLyQuanCafe.BusinessObject;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyQuanCafe.BusinessLogic
{
    class BanBL
    {
        KetNoiDuLieu connect = new KetNoiDuLieu();
        public DataTable LayTatCaBan()
        {
            string sql = "SELECT MaBan, MaKV, ViTriBan, TinhTrang FROM BAN";
            return connect.LayDanhSachDuLieu(sql);
        }
        public DataTable DSBan()
        {
            string sql = "SELECT MaBan,MaKV, ViTriBan FROM BAN";
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable DSBanKV()
        {
            string sql = "SELECT BAN.MaBan, KHUVUC.TenKV, BAN.ViTriBan FROM BAN,KHUVUC WHERE BAN.MaKV = KHUVUC.MaKV AND KHUVUC.LoaiKV='1'";
            return connect.LayDanhSachDuLieu(sql);
        }

        //--------Sửa Sáng 9/8
        public DataTable DSBanTheoMaKV(string value)
        {
            string sql = "SELECT BAN.MaBan FROM BAN WHERE MaKV = '" + value + "'";
            return connect.LayDanhSachDuLieu(sql);
        }
        public bool UpGopBan(string value1, string value2)
        {
            string sql = string.Format("UPDATE BAN SET GopBan='{0}' WHERE MaBan ='{1}'", value2, value1);
            if (connect.ExecuteQuery(sql))
                return true;
            return false;
        }
        public DataTable DSBanGopTheoKV(string maKV)
        {
            string sql = string.Format("SELECT DISTINCT GopBan FROM BAN WHERE MaKV = '{0}'", maKV);
            return connect.LayDanhSachDuLieu(sql);
        }
        public DataTable CheckGopBan(string value)
        {
            string sql = "SELECT * FROM BAN WHERE GopBan = '" + value + "'";
            return connect.LayDanhSachDuLieu(sql);
        }
        public DataTable GetGopBan(string value)
        {
            string sql = string.Format("SELECT MaBan FROM BAN WHERE GopBan = '" + value + "'");
            return connect.LayDanhSachDuLieu(sql);
        }

        //-----------


        public DataTable DSKV()
        {
            string sql = "SELECT BAN.MaBan, Ban.MaKV, KHUVUC.TenKV, BAN.ViTriBan FROM BAN,KHUVUC WHERE BAN.MaKV = KHUVUC.MaKV";
            return connect.LayDanhSachDuLieu(sql);
        }
        public DataTable GetTinhTrang(string value)
        {
            string sql = "SELECT TinhTrang FROM BAN WHERE MaBan = '" + value + "'";
            return connect.LayDanhSachDuLieu(sql);
        }
        public DataTable GetBanTheoTT(string value)
        {
            string sql = "SELECT MaBan FROM BAN WHERE TinhTrang = '" + value + "'";
            return connect.LayDanhSachDuLieu(sql);
        }


        //---------------------Vũ-----------------
        public DataTable LayDanhSachBanKhuVuc(string maKV)
        {
            string sql = string.Format("SELECT MaBan, MaKV, ViTriBan, TinhTrang FROM BAN WHERE MaKV = '{0}'", maKV);
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable LayDanhSachBanTheoTinhTrang(int tinhtrangBan)
        {
            string sql = string.Format("SELECT MaBan, MaKV, ViTriBan, TinhTrang FROM BAN WHERE TinhTrang = '{0}'", tinhtrangBan);
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable LayDanhSachBanTheoMaBan(string maban)
        {
            string sql = string.Format("SELECT MaBan, MaKV, ViTriBan, TinhTrang FROM BAN WHERE MaBan = '{0}'", maban);
            return connect.LayDanhSachDuLieu(sql);
        }

        public DataTable LayDanhSachTimKiemBanTheoMaBan(string maban)
        {
            string sql = string.Format("SELECT MaBan, KHUVUC.TenKV, TinhTrang FROM BAN, KHUVUC WHERE BAN.MaKV = KHUVUC.MaKV AND MaBan = '{0}'", maban);
            return connect.LayDanhSachDuLieu(sql);
        }

        public bool CapNhatTinhTrangBan(int maBan, int tinhtrangBan)
        {
            string sql = string.Format("UPDATE BAN SET TinhTrang = '{0}' WHERE MaBan = '{1}'", tinhtrangBan, maBan);
            if (connect.ExecuteQuery(sql))
            {
                //MessageBox.Show("Chuyển Đổi Trạng Thái Thành Công", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return true;
            }
            return false;
        }

        //---------------------Hiếu------------------
        public bool CheckBan(int value)
        {
            if (connect.KiemTraGiaTriInt("BAN", "MaBan", value))
                return true;
            return false;
        }

        public bool CheckViTri(int value)
        {
            return false;
        }
        public bool ThemBan(BanBO banBO)
        {
            if (TestSave(banBO))
            {
                string sql = string.Format("INSERT INTO BAN (MaBan, MaKV, ViTriBan, TinhTrang, GopBan) VALUES ('{0}', '{1}', '{2}', 1, '{3}')", banBO.MaBan, banBO.MaKV, banBO.ViTriBan,banBO.MaBan);
                if (connect.ExecuteQuery(sql))
                {
                    MessageBox.Show("Thêm Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return false;
        }

        public bool SuaBan(BanBO banBO)
        {
            if (TestSave(banBO))
            {
                string sql = string.Format("UPDATE BAN SET MaKV = '{0}', ViTriBan = '{1}' " + "WHERE MaBan = '{2}'", banBO.MaKV, banBO.ViTriBan, banBO.MaBan);
                if (connect.ExecuteQuery(sql))
                {
                    MessageBox.Show("Sửa Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return false;
        }

        public bool SuaTinhTrang(BanBO banBO)
        {
            string sql = string.Format("UPDATE BAN SET TinhTrang = '{0}'" + "WHERE MaBan = '{1}'", banBO.TinhTrang, banBO.MaBan);
            if (connect.ExecuteQuery(sql))
            {
                //MessageBox.Show("Cập Nhật Tình Trạng Thành Công", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return true;
            }
            return false;
        }
        public bool SuaTinhTrang(string value1, string value2)
        {
            string sql = string.Format("UPDATE BAN SET TinhTrang = '{0}'" + "WHERE MaBan = '{1}'", value1, value2);
            if (connect.ExecuteQuery(sql))
            {
                MessageBox.Show("Sửa Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return true;
            }
            return false;
        }
        public bool XoaBan(string value)
        {
            string sql = string.Format("DELETE FROM BAN WHERE MaBan = '" + value + "'");
            if (connect.ExecuteQuery(sql))
            {
                MessageBox.Show("Xóa Thành Công!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return true;
            }
            return false;
        }

        public bool TestSave(BanBO banBO)
        {
            if (banBO.MaBan.Equals(""))
            {
                MessageBox.Show("Mã Bàn Không Hợp Lệ!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            
            if (banBO.ViTriBan<0 )
            {
                MessageBox.Show("Vị Trí Không Hợp Lệ!", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            return true;
        }

    }
}
